package com.apes.fico.finance.service;

import com.apes.framework.api.DomainService;
import com.apes.framework.plugin.esb.api.SimpleRequest;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @ClassName AccountBalanceService
 * @Description TODO
 * @Author pengyb
 * @Date 2019/3/18 15:47
 * @Version 1.0
 **/
@Service("accountBalanceService")
public class AccountBalanceService extends DomainService {

    @Autowired
    private EntityManager entityManager;

    /**
     * 查询财务余额表
     *
     * @param request
     * @return
     */
    public List<Map<String, Object>> findAll(SimpleRequest request) {
        //取值
        String company = request.get("company");
        int year = request.get("year"); //财务年度起
        int stopYear = request.get("stopYear"); //财务年度止
        int period = request.get("period"); //会计期间起
        int stopPeriod = request.get("stopPeriod"); //会计期间止
        String classes = request.get("classes");    //科目级次
        String stopClasses = request.get("stopClasses");    //科目级次
        String accCode = request.get("accCode"); ///科目id
//        if (accCode == null && classes == null) throw new RuntimeException("请选择级别或者科目！");
        Boolean checkAux = request.get("checkOne");
        Boolean checkAcc = request.get("checkAcc");
        String auxName = request.get("auxName");
        String aux = checkAux ? ",CASE WHEN LENGTH(B.CODE) != 8 THEN '' WHEN SUBSTR(A.NAME,0,4) = '现金流量' THEN '' WHEN INSTR(A.NAME,', 现金流量') > 1 THEN" +
                "                      SUBSTR(A.NAME,1,INSTR(A.NAME,',',-1)-1) ELSE A.NAME END" : ",null";
        String acc = checkAcc ? "SUM(A.DEBIT)+SUM(A.UN_DEBIT) DEBIT, SUM(A.CREDIT)+SUM(A.UN_CREDIT) CREDIT" : "SUM(A.DEBIT) DEBIT, SUM(A.CREDIT) CREDIT";
        String acc2 = checkAcc ? "SUM(A.CLOSING_BALANCE + A.UN_DEBIT - A.UN_CREDIT)" : "SUM(A.CLOSING_BALANCE)";
        String auxNameCon = Objects.nonNull(auxName) ? " AND A.NAME LIKE '%" + auxName + "%'" : "AND 1= 1 ";
        String accCodeCon = Objects.nonNull(accCode) ? " AND B.CODE = '" + accCode + "'" : "AND 1 = 1 ";
        String accClassesCon = Objects.nonNull(stopClasses) ? "AND (B.CLASSES BETWEEN '" + classes + "' AND '" + stopClasses + "')" : "AND 1 = 1 ";


        List<Map<String, Object>> voucherItemsMaps;
        //拼接SQL
        StringBuffer voucher_buffer = new StringBuffer();

        /**
         * 字段备注：
         * OPENING_BALANCE 期初
         * CREDIT 贷方本期发生额 CREDIT_YEAR 贷方本年累计 CREDIT_OPENING_BALANCE 贷方期初余额 CREDIT_ENDING_BALANCE 贷方期末余额
         * DEBIT 借方本期发生额 DEBIT_YEAR 借方本年累计 DEBIT_OPENING_BALANCE 借方期初余额 DEBIT_ENDING_BALANCE 借方期末余额
         */
        voucher_buffer.append(
                "SELECT A.YEAR, B.ID, B.CODE, B.NAME, A.COMPANY_ID,"+ acc +"," +
                "       SUM(A.OPENING_BALANCE) OPENING_BALANCE, SUM(A.CLOSING_BALANCE) CLOSING_BALANCE, SUM(DEBIT_YEAR) DEBIT_YEAR,SUM(CREDIT_YEAR) CREDIT_YEAR,  " +
                "       (CASE WHEN SUM(A.OPENING_BALANCE) < 0 THEN 0 ELSE SUM(A.OPENING_BALANCE) END) DEBIT_OPENING_BALANCE,  " +
                "       (CASE WHEN SUM(A.OPENING_BALANCE) < 0 THEN SUM(A.OPENING_BALANCE) * -1 ELSE 0 END) CREDIT_OPENING_BALANCE,  " +
                "       (CASE WHEN " + acc2 + " < 0 THEN 0 ELSE " + acc2 + " END) DEBIT_ENDING_BALANCE,  " +
                "       (CASE WHEN " + acc2 + " < 0 THEN " + acc2 + " * -1 ELSE 0 END) CREDIT_ENDING_BALANCE "+ aux + " AUX_NAME  " +
                "  FROM (SELECT YEAR,COMPANY_ID, ACC_OPERATING_CHART_ID, NAME, OPENING_BALANCE, 0 DEBIT, 0 CREDIT, 0 UN_DEBIT, 0 UN_CREDIT, 0 CLOSING_BALANCE,  " +
                "                0 DEBIT_YEAR, 0 CREDIT_YEAR  " +
                "           FROM V_GL_CHART_ALL   " + //期初
                "          WHERE PERIOD = '" + period + "' " +
                "         UNION ALL  " +
                "         SELECT YEAR,COMPANY_ID, ACC_OPERATING_CHART_ID, NAME, 0 OPENING_BALANCE, DEBIT, CREDIT,UN_DEBIT,UN_CREDIT, 0 CLOSING_BALANCE,  " +
                "                0 DEBIT_YEAR, 0 CREDIT_YEAR  " +
                "           FROM V_GL_CHART_ALL   " + //-- 本期发生数
                "          WHERE (PERIOD BETWEEN '" + period + "' AND '" + stopPeriod + "') AND PERIOD != 202202" +
                "         UNION ALL  " +
                "         SELECT YEAR,COMPANY_ID, ACC_OPERATING_CHART_ID, NAME, 0 OPENING_BALANCE, 0 DEBIT, 0 CREDIT,0 UN_DEBIT, 0 UN_CREDIT, CLOSING_BALANCE,  " +
                "                0 DEBIT_YEAR, 0 CREDIT_YEAR  " +
                "           FROM V_GL_CHART_ALL   " + //-- 期末
                "          WHERE PERIOD = '" + stopPeriod + "' " +
                "         UNION ALL  " +
                "         SELECT YEAR,COMPANY_ID, ACC_OPERATING_CHART_ID, NAME, 0 OPENING_BALANCE, 0 DEBIT, 0 CREDIT,0 UN_DEBIT, 0 UN_CREDIT, 0 CLOSING_BALANCE,  " +
                "                DEBIT DEBIT_YEAR, CREDIT CREDIT_YEAR  " +
                "           FROM V_GL_CHART_ALL   " + //-- 年度发生数
                "          WHERE (PERIOD BETWEEN '" + stopYear + "01" + "' AND '" + stopPeriod + "') AND PERIOD != 202202" +
                ") A, MAS_ACC_OPERATING_CHART B  " +
                " WHERE A.ACC_OPERATING_CHART_ID = B.ID  " +
                "   AND A.COMPANY_ID ='" + company + "'" +
                        accClassesCon + accCodeCon + auxNameCon +
                " GROUP BY B.ID, B.CODE, B.NAME, A.COMPANY_ID, A.YEAR " + aux + " ORDER BY B.CODE ");

        //取结果集
        Query query = entityManager.createNativeQuery(voucher_buffer.toString());
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        voucherItemsMaps = query.getResultList(); //结果集

        return voucherItemsMaps;

    }

}
